package org.openfans.persistence;

import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.openfans.domain.Article;
import org.openfans.domain.Auth;
import org.openfans.domain.Fans;
import org.openfans.domain.FansSnippet;
import org.openfans.domain.Feed;
import org.openfans.domain.FeedItem;
import org.openfans.domain.Group;
import org.openfans.domain.Message;
import org.openfans.domain.Page;
import org.openfans.domain.PreFans;
import org.openfans.domain.RootTag;
import org.openfans.domain.Snippet;
import org.openfans.domain.Tag;
import org.openfans.util.EncryptUtil;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

public class Fetcher extends HibernateDaoSupport {
	public Auth authenticate(Auth auth) {
		List l = getHibernateTemplate().find(
				"from Auth where email ='" + auth.getEmail() + "' and passwd='"
						+ EncryptUtil.encode(auth.getPasswd()) + "'");
		if (l != null && l.size() > 0) {
			return (Auth) l.get(0);
		}
		return null;
	}

	public Fans authenticateFans(String encryptStr) {
		String id = EncryptUtil.getId(encryptStr);
		if (null == id) {
			return new PreFans();
		} else {
			Auth auth = getAuth(Integer.parseInt(id));
			if (auth != null
					&& EncryptUtil.bind(id, auth.getPasswd())
							.equals(encryptStr)) {
				return auth.getFans();
			} else {
				return new PreFans();
			}
		}
	}

	public List<Feed> getAllFeeds() {
		return getList("from Feed");
	}

	public Article getArticle(int id) {
		return (Article) getHibernateTemplate().get(Article.class,
				new Integer(id));
	}

	private Auth getAuth(int id) {
		return (Auth) getHibernateTemplate().get(Auth.class, new Integer(id));
	}

	public Auth getAuthByActivateCode(String code) {
		List l = getHibernateTemplate().find(
				"from Auth where code ='" + code + "'");
		if (l != null && l.size() > 0) {
			return (Auth) l.get(0);
		}
		return null;
	}

	public int getCount(final String query) {
		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						Query q = session.createQuery(query);
						return ((Integer) (q.iterate().next())).intValue();
					}
				});
	}

	public Fans getFans(int id) {
		return (Fans) getHibernateTemplate().get(Fans.class, new Integer(id));
	}

	public Fans getFans(String name) {
		List l = getHibernateTemplate().find(
				"from Fans where name ='" + name + "'");
		if (l != null && l.size() > 0) {
			return (Fans) l.get(0);
		} else {
			throw new NotFoundException("user not found");
		}
	}

	public Feed getFeed(int id) {
		return (Feed) getHibernateTemplate().get(Feed.class, new Integer(id));
	}

	public FeedItem getFeedItem(int id) {
		return (FeedItem) getHibernateTemplate().get(FeedItem.class,
				new Integer(id));
	}

	public Group getGroup(int id) {
		return (Group) getHibernateTemplate().get(Group.class, new Integer(id));

	}

	public Message getMessage(int id) {
		return (Message) getHibernateTemplate().get(Message.class,
				new Integer(id));
	}

	private List getList(final String query) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException {
				Query q = session.createQuery(query);
				return q.list();
			}
		});
	}

	private List getList(final String query, final int start, final int size) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException {
				Query q = session.createQuery(query);
				q.setFirstResult(start);
				q.setMaxResults(size);

				return q.list();
			}
		});
	}

	public Page getPagedFeedItems(String order, int pageNum, int pageSize) {
		String sql = "from FeedItem feedItem ";
		return getPagedObject(sql, order, pageNum, pageSize);
	}

	public Page getPagedFansSubFeedItems(Fans fans, String order, int pageNum,
			int pageSize) {
		String condition = "where feedItem.feed.feedId in (select fansSubFeed.feed.feedId from FansSubFeed fansSubFeed where fansSubFeed.fans.fansId="
				+ fans.getFansId() + ") ";
		return getPageFeedItemsWithCondition(order, condition, pageNum,
				pageSize);
	}

	public Page getPagedFansFeedItems(Fans fans, String order, int pageNum,
			int pageSize) {
		String condition = "where fans_id =" + fans.getFansId();
		return getPageFeedItemsWithCondition(order, condition, pageNum,
				pageSize);
	}

	public Page getPagedTagFeedItems(Tag tag, String order, int pageNum,
			int pageSize) {
		String condition = "where tag_id =" + tag.getTagId();
		return getPageFeedItemsWithCondition(order, condition, pageNum,
				pageSize);
	}

	public Page getPagedFeedFeedItems(Feed feed, String order, int pageNum,
			int pageSize) {
		String condition = "where feedItem.feed.feedId=" + feed.getFeedId();
		return getPageFeedItemsWithCondition(order, condition, pageNum,
				pageSize);
	}

	private Page getPageFeedItemsWithCondition(String order, String condition,
			int pageNum, int pageSize) {
		String sql = "from FeedItem feedItem " + condition;
		return getPagedObject(sql, order, pageNum, pageSize);
	}

	public List getRecentArticles(int pageSize, String order, String type) {
		return getPagedList("from " + type + " order by " + order, 1, pageSize);
	}

	/**
	 * 根据一定的查询字符串,获得该用户收藏的文章<br>
	 * 直接给持久层字符串,增加了一定的业务,但便于扩展<br>
	 * 
	 * @param fansId
	 * @param query
	 *            用户输入的查询条件
	 * @param pageNum
	 * @param pageSize
	 * @param order
	 * @return
	 */
	public Page searchFansSavedArticles(int fansId, String query, int pageNum,
			int pageSize, String order) {
		StringBuffer sb = new StringBuffer();
		sb.append(" from T_ARTICLE");
		sb
				.append(" where ARTICLE_ID in (SELECT ARTICLE_ID from T_ARTICLE_FANS where fans_id=");
		sb.append(fansId);
		sb.append(") ");

		String sql = generateSearchArticleSql(query, sb);
		return getPagedObjectByNativeSql(Article.class, sql, order, pageNum,
				pageSize);

	}

	public Page searchArticles(String query, String order, String type,
			int pageNum, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append(" from T_ARTICLE ");
		if ("TechArticle".equals(type)) {
			sb.append("where type=1 ");
		} else if ("News".equals(type)) {
			sb.append("where type=2 ");
		} else {
			sb.append("where 1=1 ");
		}
		String sql = generateSearchArticleSql(query, sb);
		return getPagedObjectByNativeSql(Article.class, sql, order, pageNum,
				pageSize);
	}

	private String generateSearchArticleSql(String query, StringBuffer sb) {
		// 不指定query则代表全部;
		if (null != query && query.length() > 0) {
			sb
					.append("and ARTICLE_ID in (SELECT ARTICLE_ID from T_ARTICLE_TAG ");
			generateTagSearchSql("article_id", query, sb);
		}
		return sb.toString();
	}

	/**
	 * 根据传入的count语句和查询语句生成Page分页对象,使用native sql
	 * 
	 * @param countSql
	 *            “from 及之后的语句”
	 * @param listSql
	 *            “from 及之后的语句”
	 * @param order
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	private Page getPagedObjectByNativeSql(Class cls, String sql, String order,
			int pageNum, int pageSize) {
		Page p = new Page();
		String countSql = "select count(*) as count " + sql;
		String listSql = "select * " + sql;
		int i = getCountByNativeSQL(countSql);
		List l = getPagedListByNativeSQL(cls, listSql, order, pageNum, pageSize);
		p.setCount(i);
		p.setList(l);
		p.setPageNum(pageNum);
		p.setPageSize(pageSize);
		p.cacPageCount();
		return p;
	}

	public Page getPagedFans(String order, int pageNum, int pageSize) {
		String sql = "from Fans  order by " + order;
		return getPagedObject(sql, order, pageNum, pageSize);
	}

	public List getPagedGroups(int pageNum, int pageSize, String order) {
		return getPagedList("from Group order by " + order, pageNum, pageSize);

	}

	private List getPagedList(String sql, int pageNum, int pageSize) {
		return getList(sql, (pageNum - 1) * pageSize, pageSize);
	}

	public Page getPagedRootTags(String order, int pageNum, int pageSize) {
		String sql = "from RootTag";
		return getPagedObject(sql, order, pageNum, pageSize);
	}

	/**
	 * 根据传入的from及之后的语句生成Page分页对象
	 * 
	 * @param countSql
	 * @param listSql
	 * @param order
	 * @param pageNum
	 * @param pageSize
	 * @return
	 */
	private Page getPagedObject(String sql, String order, int pageNum,
			int pageSize) {
		Page p = new Page();
		String countSql = "select count(*) " + sql;
		int i = getCount(countSql);
		List l = getPagedList(sql + " order by " + order, pageNum, pageSize);
		p.setCount(i);
		p.setList(l);
		p.setPageNum(pageNum);
		p.setPageSize(pageSize);
		p.cacPageCount();
		return p;
	}

	public Page getPagedFeed(int pageNum, int pageSize) {
		return getPagedObject("from Feed", "subscribeCount desc", pageNum,
				pageSize);
	}

	public List getPagedTags(int pageNum, int pageSize, String order) {
		return getPagedList("from Tag  where type=0 order by " + order,
				pageNum, pageSize);
	}

	/**
	 * 取最后的几个公告，使用seq实现置顶功能
	 * 
	 * @param i
	 * @return
	 */
	public List getRecentBoardItem(int i) {
		return getList("from BoardItem order by seq desc", 0, i);
	}

	public List getRecommendRootTags() {
		return getHibernateTemplate().find(
				"from RootTag where recommendated is true");
	}

	public List getRelArticles(Article article) {
		final int articleId = article.getArticleId();
		final String s = "select * from T_ARTICLE where ARTICLE_ID in (SELECT ARTICLE_ID from T_ARTICLE_TAG where TAG_ID in (select TAG_ID from T_ARTICLE_TAG where article_id="
				+ articleId + ")) && ARTICLE_ID<>" + articleId;
		return getListByNativeSQL(Article.class, s);
	}

	private List getListByNativeSQL(final Class cls, final String sql) {
		return (List) getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException {
				return session.createSQLQuery(sql).addEntity(cls).list();

			}
		});
	}

	private Integer getCountByNativeSQL(final String sql) {
		return (Integer) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						return (Integer) (session.createSQLQuery(sql)
								.addScalar("count", Hibernate.INTEGER)
								.uniqueResult());
					}
				});
	}

	private List getPagedListByNativeSQL(final Class cls, String sql,
			String order, int pageNum, int pageSize) {
		final String s = sql + " order by " + order + " limit " + (pageNum - 1)
				* pageSize + "," + pageSize;
		return getListByNativeSQL(cls, s);
	}

	public Tag getTag(int id) {
		return (Tag) getHibernateTemplate().get(Tag.class, new Integer(id));

	}

	public Tag getTagByTitle(String title) {
		List l = getHibernateTemplate().find(
				"from Tag where title='" + title + "'");
		if (l != null && l.size() > 0) {
			return (Tag) l.get(0);
		}
		return null;
	}

	public List getTagRelRootTag(Tag tag) {
		return getHibernateTemplate()
				.find(
						"select tag from RootTag tag,TagRelation tr where tr.tag=tag and tr.rtag.tagId="
								+ tag.getTagId());
	}

	public List getUserArticle(String name) {
		return getHibernateTemplate().find(
				"from Article article where article.fans.name ='" + name
						+ "' order by submit_date desc");

	}

	public List listFans() {
		return getHibernateTemplate().find("from Fans");

	}

	/**
	 * 
	 * Author:pesome<br>
	 * Time:2006-12-20 下午09:17:53<br>
	 * 
	 * @param pageNum
	 * @param pageSize
	 * @param fansId
	 * @param order
	 * @return
	 */
	public List getPagedPostMessages(int pageNum, int pageSize, int fansId,
			String order) {
		return getPagedList("from Message  m where m.fromFans.fansId=" + fansId
				+ " order by " + order, pageNum, pageSize);
	}

	public List getPagedReceiveMessages(int pageNum, int pageSize, int fansId,
			String order) {
		return getPagedList("from Message  m where m.toFans.fansId=" + fansId
				+ " order by " + order, pageNum, pageSize);
	}

	public int getNewMessagesCount(int fansId) {
		return getCount("select count(*) from Message m where m.status="
				+ Message.UNREAD + " and m.toFans.fansId=" + fansId);
	}

	public List listGroup() {
		return getHibernateTemplate().find("from Group");
	}

	public List searchTags(String title) {
		return getHibernateTemplate().find(
				"from Tag tag where tag.title ='" + title + "'");
	}

	/**
	 * 获得同一个feed下的其它feedItem
	 * 
	 * @param feedItem
	 * @return
	 */
	public List getRelFeedItems(FeedItem feedItem) {
		final int feedId = feedItem.getFeed().getFeedId();
		final String sql = "select * from T_FeedItem where FEED_ID =" + feedId;
		return getPagedListByNativeSQL(FeedItem.class, sql, " id desc", 1, 20);
	}

	public List getAllRootTags() {
		return getHibernateTemplate().find("from RootTag");
	}

	public List getTagFeedItem(Tag tag) {
		return getHibernateTemplate().find(
				"from TagFeedItem t where t.tag.tagId=" + tag.getTagId()
						+ " order by id desc");
	}

	public Snippet getSnippetByUrl(String url) {
		List l = getHibernateTemplate().find(
				"from Snippet where url='" + url + "'");
		if (l != null && l.size() > 0) {
			return (Snippet) l.get(0);
		}
		return null;
	}

	public List getRecentRootTags(int i, String order) {
		return getPagedList("from RootTag" + " order by " + order, 1, i);
	}

	public List getRecentFans(int i, String order) {
		return getPagedList("from Fans" + " order by " + order, 1, i);
	}

	public Page getPagedFansSnippet(Fans fans, String query, String order,
			int pageNum, int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append(" from T_FANS_SNIPPET where fans_id=");
		sb.append(fans.getFansId());
		String sql = generateSearchFansSnippetSql(query, sb);
		return getPagedObjectByNativeSql(FansSnippet.class, sql, order,
				pageNum, pageSize);
	}

	public Page getPagedSnippet(String query, String order, int pageNum,
			int pageSize) {
		StringBuffer sb = new StringBuffer();
		sb.append(" from T_SNIPPET where privat=false");
		String sql = generateSearchSnippetSql(query, sb);
		return getPagedObjectByNativeSql(Snippet.class, sql, order, pageNum,
				pageSize);
	}

	private String generateSearchSnippetSql(String query, StringBuffer sb) {
		// 不指定query则代表全部;
		if (null != query && query.length() > 0) {
			sb.append(" and id in (SELECT snippet_id from T_SNIPPET_TAG ");

			generateTagSearchSql("snippet_id", query, sb);
		}
		return sb.toString();
	}

	private String generateSearchFansSnippetSql(String query, StringBuffer sb) {
		// 不指定query则代表全部;
		if (null != query && query.length() > 0) {
			sb
					.append(" and id in (SELECT fans_snippet_id from T_FANS_SNIPPET_TAG ");

			generateTagSearchSql("fans_snippet_id", query, sb);
		}
		return sb.toString();
	}

	/**
	 * 在Tag搜索中需与tag表联合查询的相同逻辑
	 * 
	 * @param column
	 * @param query
	 * @param sb
	 */
	private void generateTagSearchSql(String column, String query,
			StringBuffer sb) {
		sb.append("where tag_id in (");
		String[] tags = query.split(" ");
		int i = 0;
		for (int j = 0; j < tags.length; j++) {
			if (!" ".equals(tags[j])) {
				sb.append("(select tag_id from T_TAG where title='");
				sb.append(tags[j]);
				sb.append("'),");
				i++;
			}
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(")");
		sb.append(" group by ");
		sb.append(column);
		sb.append(" having count(*)>=");
		sb.append(i);
		sb.append(")");
	}

	public Snippet getSnippet(int id) {
		return (Snippet) getHibernateTemplate().get(Snippet.class,
				new Integer(id));

	}

	public Page getPagedRootTagRemarks(RootTag tag, String order, int pageNum,
			int pageSize) {
		// TODO Auto-generated method stub
		return getPagedObject("from RootTagRemark remark where remark.tag.tagId="+tag.getTagId(), order, pageNum,
				pageSize);
	}

	public List getFeedsByTag(Tag tag) {
		// TODO Auto-generated method stub
		return getList("from TagFeed tf where tf.tag.tagId="+tag.getTagId());
	}

	public List getSnippetsByTag(Tag tag) {
		// TODO Auto-generated method stub
		return getList("from Snippet where ");
	}
}